//#[macro_use]
//extern crate mysql;

use chrono::naive::NaiveDateTime;
use chrono::prelude::*;
use mysql as my;
use mysql::prelude::*;
use mysql::Value;
use mysql::Value::Date;
use std::sync::Arc;
// use mysql_common::value::convert;

#[derive(Debug, PartialEq, Eq)]
struct Area {
    sysno: i32,
    areacode: Option<String>,
    areaname: Option<String>,
}

#[derive(Debug)]
struct Name {
    OrderKind: Option<i32>,
    OrderSysNo: Option<i64>,
    CustomerSysNo: Option<i64>,
    CustomerName: Option<String>,
    CustomerPhone: Option<String>,
    SourceType: Option<i32>,
    WarehouseId: Option<i64>,
    SaleId: Option<i64>,
    SKUId: Option<i32>,
    SaleQty: Option<i32>,
    SaleAmt: Option<f64>,
    CostAmt: Option<String>,
    CostQty: Option<String>,
    BusinessDate: Option<String>,
    DividendDate: Option<String>,
    StockOutItemSysNo: Option<String>,
    ReturnStockInItemSysNo: Option<String>,
    KeyStockItemId: Option<String>,
    DisCountAmount: Option<String>,
    ChangeAmount: Option<String>,
    CoinPay: Option<String>,
    UseFortunerollMoney: Option<String>,
    ValueDepositAmount: Option<String>,
    Integraldeduction: Option<String>,
    DeductionCouponAmount: Option<String>,
    RecycleAmount: Option<String>,
    ServiceCode: Option<String>,
    SrcIMEICode: Option<String>,
    SrcSaleDate: Option<String>,
    SrcSaleQty: Option<String>,
    SrcSaleAmt: Option<String>,
    SrcRealSaleQty: Option<String>,
}

#[derive(Debug, PartialEq)]
pub struct TCX {
    OrderKind: Option<i64>,
    OrderSysNo: Option<i64>,
    CustomerSysNo: Option<String>,
    CustomerName: Option<String>,
    CustomerPhone: Option<String>,
    IMEICode: Option<String>,
    OwnWarehouseId: Option<i64>,
    WarehouseId: Option<i64>,
    SaleId: Option<i64>,
    SKUId: Option<i64>,
    SaleQty: Option<i32>,
    SaleAmt: Option<f64>,
    CostAmt: Option<f64>,
    cost_qty: Option<i32>,
    RealSaleQty: Option<i32>,
    BusinessDate: Option<String>,
    StockOutItemSysNo: Option<i64>,
    ReturnStockInItemSysNo: Option<i64>,
    KeyStockItemId: Option<String>,
    DisCountAmount: Option<f64>,
    ChangeAmount: Option<f64>,
    CoinPay: Option<f64>,
    UseFortunerollMoney: Option<f64>,
    ValueDepositAmount: Option<f64>,
    Integraldeduction: Option<f64>,
    DeductionCouponAmount: Option<f64>,
    RecycleAmount: Option<f64>,
    ServiceCode: Option<String>,
    SrcIMEICode: Option<String>,
    SrcSaleDate: Option<String>,
    SrcSaleQty: Option<i32>,
    SrcSaleAmt: Option<f64>,
    SrcRealSaleQty: Option<i32>,
    RuleType: Option<i32>,
    SaleAmount1: Option<f64>,
    DividendAmount1: Option<f64>,
    SaleAmount2: Option<f64>,
    DividendAmount2: Option<f64>,
    LastUpdatedDate: Option<String>,
    CategoryName: Option<String>,
    CategoryName1: Option<String>,
    CategoryName2: Option<String>,
    CategoryName3: Option<String>,
    AreaID: Option<i64>,
    AareaName: Option<String>,
    WareHouseName: Option<String>,
    OwnWarehouseName: Option<String>,
    WareHouseType: Option<String>,
    warehouse_star: Option<String>,
    brand_id: Option<i64>,
    brand_name: Option<String>,
    spu_id: Option<i64>,
    spu_name: Option<String>,
    sku_name: Option<String>,
    erp_code: Option<String>,
    sale_name: Option<String>,
}

pub fn mod_name() {
    println!("{:?}", "datacheck");
}

pub fn check() {
    let pool = my::Pool::new("mysql://huawei:PyzMeJjq3J6CFPQx@192.168.31.58:3307/hw_ocss").unwrap();

    println!("好像 创建表 执行成功了？ {:?}", 888i32);
    let selected_payments: Vec<Area> = pool
        .prep_exec("select sysno, areacode, areaname from bsarea", ())
        .map(|result| {
            result
                .map(|x| x.unwrap())
                .map(|row| {
                    let (sysno, areacode, areaname) = my::from_row(row);
                    Area {
                        sysno: sysno,
                        areacode: areacode,
                        areaname: areaname,
                    }
                })
                .collect()
        })
        .unwrap();

    for item in &selected_payments {
        println!(" Object = {:?} ", &item);
    }
    println!("Yay! wao 全成功了？  {:?}  ", 999i32);
}

pub fn exec_sql() {
    let sql1: String = String::from(
        r"SELECT 1 AS OrderKind ,O.SysNo AS OrderSysNo ,O.CustomerSysNo ,null AS CustomerName ,null AS CustomerPhone , :salecostsourcetype AS SourceType ,O.DefaultWarehouseSysNo AS WarehouseId ,O.SalesPerson AS SaleId ,OI.ProductSysNo AS SKUId ,SOI.ProductQuantity AS SaleQty ,(OI.SalesUnitPrice * SOI.ProductQuantity) AS SaleAmt ,CK.CostAmount AS CostAmt ,0 AS CostQty ,SO.StockOutDate AS BusinessDate ,SO.StockOutDate AS DividendDate ,SOI.SysNo AS StockOutItemSysNo ,NULL AS ReturnStockInItemSysNo ,SOI.SysNo AS KeyStockItemId ,OI.DisCountAmount ,OI.ChangeAmount ,OI.CoinPay ,OI.UseFortunerollMoney ,OI.ValueDepositAmount ,OI.Integraldeduction ,OI.DeductionCouponAmount ,OI.RecycleAmount ,NULL AS ServiceCode ,NULL AS SrcIMEICode ,NULL AS SrcSaleDate ,NULL AS SrcSaleQty ,NULL AS SrcSaleAmt ,NULL AS SrcRealSaleQty FROM SoOrder AS O INNER JOIN SoOrderItem AS OI ON OI.OrderSysNo = O.SysNo INNER JOIN WHStockOut AS SO ON SO.OrderSysNo = O.SysNo AND SO.SourceType = :salestockoutsourcetype AND SO.`Status` > :salestockoutstatus AND SO.StockOutDate >=:begindate AND SO.StockOutDate <=:enddate INNER JOIN WhStockOutItem AS SOI ON SOI.StockOutSysNo = SO.SysNo AND SOI.OrderSysNo = O.SysNo AND SOI.OrderItemSysNo = OI.SysNo LEFT JOIN (SELECT A.SourceId ,SourceItemsId ,SKUId ,SUM(Quantity) AS Quantity ,SUM(CostAmount) AS CostAmount FROM CostKeeping AS A INNER JOIN CostKeepingItems AS B ON B.CostKeepingId = A.Id WHERE A.SourceType = :salecostsourcetype GROUP BY A.SourceId,SourceItemsId,SKUId ) AS CK ON CK.SourceId = SO.SysNo AND CK.SourceItemsId = SOI.SysNo AND CK.SKUId = SOI.ProductSysNo;",
    );
    let pool = my::Pool::new("mysql://huawei:PyzMeJjq3J6CFPQx@192.168.31.58:3307/hw_ocss").unwrap();
    let res: Vec<i32> = pool
        .prep_exec(
            sql1,
            params! {
                "begindate" => String::from("2020-03-02"),
                "enddate" => String::from("2020-03-05"),
                "salestockoutsourcetype" => 60, // 必须全小写 非常重要否则无法执行
                "salestockoutstatus" => 10,
                "salecostsourcetype" => 61,
            },
        )
        .map(|result| {
            result
                .map(|x| x.unwrap())
                .map(|row| {
                    //let (OrderKind) = my::from_row(row).get(0).expect("没有kind");
                    //let (OrderKind, OrderSysNo, CustomerSysNo, CustomerName, CustomerPhone, SourceType, WarehouseId, SaleId, SKUId, SaleQty, SaleAmt, CostAmt, CostQty, BusinessDate, DividendDate, StockOutItemSysNo, ReturnStockInItemSysNo, KeyStockItemId, DisCountAmount, ChangeAmount, CoinPay, UseFortunerollMoney, ValueDepositAmount, Integraldeduction, DeductionCouponAmount, RecycleAmount, ServiceCode, SrcIMEICode, SrcSaleDate, SrcSaleQty, SrcSaleAmt, SrcRealSaleQty) = my::from_row(row);
                    //let (OBJx) = my::from_row(row);
                    //let (OrderKind) = row.get(0).expect("没有kind")
                    let objx: i64 = row.get(0).expect("null");
                    println!("obj {:?} type ", objx);
                    1
                })
                .collect()
        })
        .unwrap();

    for n in &res {
        println!("Yay! wao 全成功了？  {:?}  ", n);
    }
    println!("run sql 全成功了？  {:?}  ", 100i32);
}

fn db_no_exec() {
    // Let's insert payments to the database
    // We will use into_iter() because we do not need to map Stmt to anything else.
    // Also we assume that no error happened in `prepare`.
    // for mut stmt in pool.prepare(r"INSERT INTO payment
    //                                    (customer_id, amount, account_name)
    //                                VALUES
    //                                    (:customer_id, :amount, :account_name)").into_iter() {
    //     for p in payments.iter() {
    //         // `execute` takes ownership of `params` so we pass account name by reference.
    //         // Unwrap each result just to make sure no errors happened.
    //         stmt.execute(params!{
    //             "customer_id" => p.customer_id,
    //             "amount" => p.amount,
    //             "account_name" => &p.account_name,
    //         }).unwrap();
    //     }
    // }

    // params!{
    //     "SaleStockOutSourceType" => 60,
    //     "SaleStockOutStatus" => 10,
    //     "BeginDate" => String::from("2020-03-02"),
    //     "EndDate" => String::from("2020-03-05"),
    //     "SaleCostSourceType" => 61,
    //     // "RepairOrderStatus" => 100,
    //     // "RepairStockOutStatus" => 20,
    //     // "RepairCostSourceType" => 51,
    //     // "ReturnStockInStatus" => 50,
    //     // "ReturnStockInSourceType" => 20,
    //     // "ReturnCostSourceType" => 62,
    // }

    // for mut stmt in pool.prepare(r"SELECT :customer_id").into_iter() {
    //     for p in payments.iter() {
    //         // `execute` takes ownership of `params` so we pass account name by reference.
    //         // Unwrap each result just to make sure no errors happened.
    //         stmt.execute(params!{
    //             "customer_id" => p.customer_id,
    //             "amount" => p.amount,
    //             "account_name" => &p.account_name,
    //         }).unwrap();
    //     }
    // }
    // //
    // conn.exec_iter("SELECT 42", ()).map(|mut result| {
    // let cell = result.next().unwrap().unwrap().take(0).unwrap();
    // assert_eq!(cell, Value::Int(42i64));
    // assert_eq!(from_value::<i64>(cell), 42i64);
    // }).unwrap();
}

// pub trait ValOrDef {
//     fn val_or_def<V: Value, T>(&self: &Value, def: T) -> T
// }

// impl ValOrDef for Value {
//     pub fn val_or_def(&self, def: T) -> T {
//         match *self {
//             Value::NULL => def,
//             Value::Int(x) => self,
//             Value::UInt(x) => format!("{}", x),
//             Value::Float(x) => format!("{}", x),
//             Value::Date(y, m, d, 0, 0, 0, 0) => format!("'{:04}-{:02}-{:02}'", y, m, d),
//             Value::Date(y, m, d, h, i, s, 0) => {
//                 format!("'{:04}-{:02}-{:02} {:02}:{:02}:{:02}'", y, m, d, h, i, s)
//             }
//             Value::Date(y, m, d, h, i, s, u) => format!(
//                 "'{:04}-{:02}-{:02} {:02}:{:02}:{:02}.{:06}'",
//                 y, m, d, h, i, s, u
//             ),
//             Value::Time(neg, d, h, i, s, 0) => {
//                 if neg {
//                     format!("'-{:03}:{:02}:{:02}'", d * 24 + u32::from(h), i, s)
//                 } else {
//                     format!("'{:03}:{:02}:{:02}'", d * 24 + u32::from(h), i, s)
//                 }
//             }
//             Value::Time(neg, d, h, i, s, u) => {
//                 if neg {
//                     format!("'-{:03}:{:02}:{:02}.{:06}'", d * 24 + u32::from(h), i, s, u)
//                 } else {
//                     format!("'{:03}:{:02}:{:02}.{:06}'", d * 24 + u32::from(h), i, s, u)
//                 }
//             }
//             Value::Bytes(ref bytes) => match from_utf8(&*bytes) {
//                 Ok(string) => escaped(string, no_backslash_escape),
//                 Err(_) => {
//                     let mut s = String::from("0x");
//                     for c in bytes.iter() {
//                         s.extend(format!("{:02X}", *c).chars())
//                     }
//                     s
//                 }
//             },
//         }
//     }
// }

// pub trait ValOrDef {
//     pub fn val_or_def<T>(&val: &Value, def: T) -> T;
// }

// impl ValOrDef for i64 {
//     pub fn val_or_def(&val: &Value, def: i64) -> i64 {
//         match val {
//             Value::NULL => def,
//             _ => mysql::from_value(ov),
//         }
//     }
// }

// fn val_or_def<T>(&val: &Value, def: T) -> T {
//     match val {
//         Value::NULL => def,
//         _ => mysql::from_value(ov),
//     }
// }

// fn val_or_def(val: Option<Value>, def: Value) -> Value {
//     match val {
//         Some(Value::NULL) => def,
//         _ => val.unwrap(),
//     }
// }

pub fn query_tc() {
    let sql1: String = String::from(" SELECT 1 AS OrderKind ,O.SysNo AS OrderSysNo ,O.CustomerSysNo ,null AS CustomerName ,null AS CustomerPhone ,:salecostsourcetype AS SourceType ,O.DefaultWarehouseSysNo AS WarehouseId ,O.SalesPerson AS SaleId ,OI.ProductSysNo AS SKUId ,SOI.ProductQuantity AS SaleQty ,(OI.SalesUnitPrice * SOI.ProductQuantity) AS SaleAmt ,CK.CostAmount AS CostAmt ,0 AS CostQty ,SO.StockOutDate AS BusinessDate ,SO.StockOutDate AS DividendDate ,SOI.SysNo AS StockOutItemSysNo ,NULL AS ReturnStockInItemSysNo ,SOI.SysNo AS KeyStockItemId ,OI.DisCountAmount ,OI.ChangeAmount ,OI.CoinPay ,OI.UseFortunerollMoney ,OI.ValueDepositAmount ,OI.Integraldeduction ,OI.DeductionCouponAmount ,OI.RecycleAmount ,NULL AS ServiceCode ,NULL AS SrcIMEICode ,NULL AS SrcSaleDate ,NULL AS SrcSaleQty ,NULL AS SrcSaleAmt ,NULL AS SrcRealSaleQty FROM SoOrder AS O INNER JOIN SoOrderItem AS OI ON OI.OrderSysNo = O.SysNo INNER JOIN WHStockOut AS SO ON SO.OrderSysNo = O.SysNo AND SO.SourceType = :salestockoutsourcetype AND SO.`Status` > :salestockoutstatus AND SO.StockOutDate >=:begindate AND SO.StockOutDate <= :enddate INNER JOIN WhStockOutItem AS SOI ON SOI.StockOutSysNo = SO.SysNo AND SOI.OrderSysNo = O.SysNo AND SOI.OrderItemSysNo = OI.SysNo LEFT JOIN (SELECT A.SourceId ,SourceItemsId ,SKUId ,SUM(Quantity) AS Quantity ,SUM(CostAmount) AS CostAmount FROM CostKeeping AS A INNER JOIN CostKeepingItems AS B ON B.CostKeepingId = A.Id WHERE A.SourceType = :salecostsourcetype GROUP BY A.SourceId,SourceItemsId,SKUId ) AS CK ON CK.SourceId = SO.SysNo AND CK.SourceItemsId = SOI.SysNo AND CK.SKUId = SOI.ProductSysNo ");
    let sql2: String = String::from(" SELECT 2 AS OrderKind ,O.SysNo AS OrderSysNo ,O.LinkmanSysNo AS CustomerSysNo ,O.linkmanname AS CustomerName ,O.linkmanphone AS CustomerPhone ,:repaircostsourcetype AS SourceType ,O.DealerSysNo AS WarehouseId ,M.RepairBy AS SaleId ,OI.SKUId ,OI.Quantity AS SaleQty ,(OI.TotalAmount - OI.DiscountAmount) AS SaleAmt ,CK.CostAmount AS CostAmt ,0 AS CostQty ,O.PickupDate AS BusinessData ,O.PickupDate AS DividendDate ,SOI.SysNo AS StockOutItemSysNo ,NULL AS ReturnStockInItemSysNo ,SOI.SysNo AS KeyStockItemId ,null as DisCountAmount ,null as ChangeAmount ,null as CoinPay ,null as UseFortunerollMoney ,null as ValueDepositAmount ,null as Integraldeduction ,null as DeductionCouponAmount ,null as RecycleAmount ,O.writeoffcode as ServiceCode ,NULL AS SrcIMEICode ,NULL AS SrcSaleDate ,NULL AS SrcSaleQty ,NULL AS SrcSaleAmt ,NULL AS SrcRealSaleQty FROM T_SO_ServiceOrder AS O INNER JOIN T_SO_ServiceOrderItem AS OI ON OI.ServiceOrderSysNo = O.SysNo INNER JOIN T_SO_MobileRepair AS M ON M.ServiceOrderSysNo = O.SysNo /*维修师(营业员)*/ LEFT JOIN WHPickStockOut AS SO ON SO.ServiceSysNo = O.SysNo AND SO.`Status` = :repairstockoutstatus LEFT JOIN WHPickStockOutItem AS SOI ON SOI.PickStockOutSysNo = SO.SysNo AND SOI.ProductSysNo = OI.SKUId LEFT JOIN (SELECT A.SourceId,SourceItemsId,SKUId,SUM(Quantity) AS Quantity,SUM(CostAmount) AS CostAmount FROM CostKeeping AS A INNER JOIN CostKeepingItems AS B ON B.CostKeepingId = A.Id WHERE A.SourceType = :repaircostsourcetype GROUP BY A.SourceId,SourceItemsId,SKUId) AS CK ON CK.SourceId = SO.SysNo AND CK.SKUId = OI.SKUId WHERE O.OrderStatus = :repairorderstatus AND O.PickupDate >= :begindate AND O.PickupDate <= :enddate ");
    let sql3: String = String::from(" SELECT 3 AS OrderKind ,O.SysNo AS OrderSysNo ,O.CustomerSysNo ,null AS CustomerName ,null AS CustomerPhone ,:returncostsourcetype AS SourceType ,O.DefaultWarehouseSysNo AS WarehouseId ,O.SalesPerson AS SaleId ,RTI.ProductSysNo AS SKUId ,RTI.RMAQuantity AS SaleQty ,RTI.RefundProductAmount AS SaleAmt ,CK.CostAmount AS CostAmt ,RTI.RMAQuantity AS CostQty ,SI.LastUpdateDate AS BusinessDate ,SALET3.BusinessDate AS DividendDate ,RTI.stockoutitemsysno AS StockOutItemSysNo ,SII.SysNo AS ReturnStockInItemSysNo ,SII.SysNo AS KeyStockItemId, RT.couponamount AS DisCountAmount, SALET3.ChangeAmount, RT.refundcoin AS CoinPay, RT.srusefortunerolltotalmoney AS UseFortunerollMoney, SALET3.ValueDepositAmount, SALET3.Integraldeduction, SALET3.DeductionCouponAmount ,SALET3.RecycleAmount ,null as ServiceCode ,'' AS SrcIMEICode ,SALET3.BusinessDate AS SrcSaleDate ,SALET3.SaleQty AS SrcSaleQty ,SALET3.SaleAmt AS SrcSaleAmt ,0 AS SrcRealSaleQty FROM RcReturn RT INNER JOIN SoOrder AS O ON RT.OrderSysNo = O.SysNo INNER JOIN RcReturnItem RTI ON RT.SysNo = RTI.ReturnSysNo INNER JOIN WHStockIn SI ON SI.SourceSysNo = RT.SysNo AND SI.Status = :returnstockinstatus AND SI.SourceType = :returnstockinsourcetype AND SI.LastUpdateDate >= :begindate AND SI.LastUpdateDate < :enddate INNER JOIN WHStockInItem SII ON SII.StockInSysNo = SI.SysNo AND SII.ProductSysNo = RTI.ProductSysNo LEFT JOIN (SELECT A.SourceId,SourceItemsId,SKUId,SUM(Quantity) AS Quantity,SUM(CostAmount) AS CostAmount FROM CostKeeping AS A INNER JOIN CostKeepingItems AS B ON B.CostKeepingId = A.Id WHERE A.SourceType = :returncostsourcetype GROUP BY A.SourceId,SourceItemsId,SKUId) AS CK ON CK.SourceId = SI.SysNo AND CK.SourceItemsId = SII.SysNo AND CK.SKUId = SII.ProductSysNo INNER JOIN (SELECT O.DefaultWarehouseSysNo AS WarehouseId ,O.SalesPerson AS SaleId ,OI.ProductSysNo AS SKUId ,SOI.ProductQuantity AS SaleQty ,(OI.SalesUnitPrice * SOI.ProductQuantity) AS SaleAmt ,CK.CostAmount AS CostAmt ,0 AS CostQty ,SO.StockOutDate AS BusinessDate ,SOI.SysNo AS StockOutItemSysNo ,OI.DisCountAmount ,OI.ChangeAmount ,OI.CoinPay ,OI.UseFortunerollMoney ,OI.ValueDepositAmount ,OI.Integraldeduction ,OI.DeductionCouponAmount ,OI.RecycleAmount FROM SoOrder AS O INNER JOIN SoOrderItem AS OI ON OI.OrderSysNo = O.SysNo /*零售单明细*/ INNER JOIN WHStockOut AS SO ON SO.OrderSysNo = O.SysNo AND SO.SourceType = :salestockoutsourcetype AND SO.`Status` > :salestockoutstatus INNER JOIN WhStockOutItem AS SOI ON SOI.StockOutSysNo = SO.SysNo AND SOI.OrderSysNo = O.SysNo AND SOI.OrderItemSysNo = OI.SysNo LEFT JOIN (SELECT A.SourceId,SourceItemsId,SKUId,SUM(Quantity) AS Quantity,SUM(CostAmount) AS CostAmount FROM CostKeeping AS A INNER JOIN CostKeepingItems AS B ON B.CostKeepingId = A.Id WHERE A.SourceType = :salecostsourcetype GROUP BY A.SourceId,SourceItemsId,SKUId ) AS CK ON CK.SourceId = SO.SysNo AND CK.SourceItemsId = SOI.SysNo AND CK.SKUId = SOI.ProductSysNo ) SALET3 ON SALET3.StockOutItemSysNo = RTI.stockoutitemsysno ");
    let sql_f: String = format!(" SELECT ALLSALE.OrderKind ,ALLSALE.OrderSysNo ,ABS(ALLSALE.CustomerSysNo) AS CustomerSysNo ,IFNULL(cc.name, cc.account) AS CustomerName ,cc.mobilephonenumber AS CustomerPhone ,imei.code AS IMEICode ,ABS(imei.OwnWarehouseId) AS OwnWarehouseId ,ABS(ALLSALE.WarehouseId) AS WarehouseId, ABS(ALLSALE.SaleId) AS SaleId, ABS(ALLSALE.SKUId) AS SKUId, ALLSALE.SaleQty, ALLSALE.SaleAmt, ALLSALE.CostAmt, ALLSALE.CostQty, 0 AS RealSaleQty, ALLSALE.BusinessDate, ABS(ALLSALE.StockOutItemSysNo) AS StockOutItemSysNo, ABS(ALLSALE.ReturnStockInItemSysNo) AS ReturnStockInItemSysNo, ALLSALE.KeyStockItemId, ALLSALE.DisCountAmount, ALLSALE.ChangeAmount, ALLSALE.CoinPay, ALLSALE.UseFortunerollMoney, ALLSALE.ValueDepositAmount, ALLSALE.Integraldeduction, ALLSALE.DeductionCouponAmount, ALLSALE.RecycleAmount, ALLSALE.ServiceCode, ALLSALE.SrcIMEICode, ALLSALE.SrcSaleDate, ALLSALE.SrcSaleQty, ALLSALE.SrcSaleAmt, ALLSALE.SrcRealSaleQty, 0 AS RuleType, 0.0 AS SaleAmount1, 0.0 AS DividendAmount1, 0.0 AS SaleAmount2, 0.0 AS DividendAmount2, str_to_date('2000-01-01', '%Y-%m-%d') AS LastUpdatedDate, pc.categoryname, pc1.categoryname AS CategoryName1, pc2.categoryname AS CategoryName2, pc3.categoryname AS CategoryName3, ABS(ba.sysno) AS areaid, ba.areaname, wh.warehousename, wh1.WarehouseName as OwnWarehouseName, wh.storetype as warehousetype, wstart.StoreStar AS WarehouseStar, abs(pb.SysNo) AS BrandId ,pb.name AS BrandName ,ABS(ps.SysNo) AS SPUId ,ps.ProductName as SPUName ,pp.ProductName AS SKUName ,pp.ERPCode ,su.account as SaleName FROM (/*- 销售 -*/ {} UNION ALL /*- 维修 -*/ {} UNION ALL /*- 退换 -*/ {s3} /*- union all 结尾分界-*/ )  ALLSALE /* 补充客户数据，串号数据 */ LEFT JOIN crcustomer cc ON cc.SysNo = ALLSALE.CustomerSysNo left JOIN imeirelation ir ON ir.SourceItemsId = ALLSALE.KeyStockItemId AND ir.SKUId = ALLSALE.SKUId AND ir.SourceType = ALLSALE.SourceType left JOIN imei ON imei.Id = ir.IMEIId LEFT JOIN (select pc.sysno as categorySysno, pc.categoryname, pdp.sysno as productsysno, pdp.productname FROM pdcategoryassociation pcs INNER JOIN pdcategory pc ON pc.sysno = pcs.categorysysno INNER JOIN pdproduct pdp ON pdp.sysno = pcs.productsysno) pcc ON pcc.productsysno = ALLSALE.SKUId LEFT JOIN pdcategory pc ON pc.sysno = pcc.categorySysno LEFT JOIN pdcategory pc1 ON pc1.sysno = pc.parentsysno LEFT JOIN pdcategory pc2 ON pc2.sysno = pc1.parentsysno LEFT JOIN pdcategory pc3 ON pc3.sysno = pc2.parentsysno INNER JOIN whwarehouse wh on wh.sysno = ALLSALE.WarehouseId LEFT JOIN (SELECT group_concat(wtag.tagname) as StoreStar, wtag.warehousesysno from (SELECT wt.tagname, wht.warehousesysno from whwarehousetag wht INNER JOIN whtag wt on wt.sysno = wht.tagsysno) wtag GROUP BY wtag.warehousesysno) wstart on wstart.warehousesysno = ALLSALE.WarehouseId INNER JOIN bsarea ba on ba.sysno = wh.areasysno LEFT JOIN pdproduct pp on pp.sysno = ALLSALE.SKUId INNER JOIN pdbrand pb on pb.sysno = pp.brandsysno INNER JOIN pdspu ps on ps.sysno = pp.spusysno LEFT JOIN syuser su on su.sysno = ALLSALE.SaleId LEFT JOIN whwarehouse wh1 on wh1.sysno = imei.OwnWarehouseId AND imei.OwnWarehouseId IS NOT NULL ORDER BY BusinessDate DESC "
        , sql1, sql2, s3 = sql3);
    let mut where_sql: String = String::from(" 1=1 ");
    let pool = my::Pool::new("mysql://huawei:PyzMeJjq3J6CFPQx@192.168.31.58:3307/hw_ocss").unwrap();
    let res: Vec<i64> = pool
        .prep_exec(
            sql_f,
            params! {
                "begindate" => NaiveDate::from_ymd(2020, 3, 1).and_hms_milli(0, 0, 0, 0),//String::from("2020-03-02"),
                "enddate" => Local::now().naive_utc(),
                "salestockoutsourcetype" => 60, // 必须全小写 非常重要否则无法执行
                "salestockoutstatus" => 10,
                "salecostsourcetype" => 61,
                "repairorderstatus" => 100,
                "repairstockoutstatus" => 20,
                "repaircostsourcetype" => 51,
                "returnstockinstatus" => 50,
                "returnstockinsourcetype" => 20,
                "returncostsourcetype" => 62,
            },
        )
        .map(|result| {
            result
                .map(|x| x.unwrap())
                .map(|row| {
                    //let (OrderKind) = my::from_row(row).get(0).expect("没有kind");
                    //let (orderkind, ordersysno) = my::from_row(row);
                    let orderkind: i64 = row.get("OrderKind").unwrap();
                    let ordersysno: i64 = row.get(1).unwrap();
                    let customersysno: i64 = row.get(2).unwrap(); //i64 可以获取
                    let customername: String = row.get(3).unwrap(); // id形式获取值，可以获取string类型
                    println!("{:?}", customername);
                    let saleamt: f64 = row.get("SaleAmt").unwrap(); // 列名字字符串形式获取值
                    println!("{:?}", saleamt);
                    let businessdate: NaiveDateTime = row.get("BusinessDate").unwrap(); // 时间用的chrono的时间类型
                    println!("{:?}", businessdate);

                    let o: Option<Value> = row.get("OwnWarehouseId");
                    println!("o is null ,val is : {:?}", o);

                    let ownwarehouseid: Value = row.get("OwnWarehouseId").unwrap();
                    println!("ownwarehouseid is null ,val is : {:?}", ownwarehouseid);

                    let ov: Value = row.get("OwnWarehouseId").unwrap();
                    let mut output: i64 = -1;
                    match ov {
                        Value::NULL => output = 0i64,
                        _ => output = mysql::from_value::<i64>(ov), //= row.get("OwnWarehouseId").unwrap()
                    }
                    println!("ownwarehouseid is null ,val is : {:?}", output);

                    // for col in row.columns().clone().iter() {
                    //     println!(" columns {:?}", col.name_str());
                    // }
                    //let objx: i64 = row.get(0).expect("null");
                    // TCX {
                    //     OrderKind : Some(orderkind),
                    //     OrderSysNo : Some(ordersysno),
                    //     ..
                    // }

                    customersysno
                })
                .collect()
        })
        .unwrap();

    for n in &res {
        println!("Yay! wao 全成功了？  {:?}  ", n);
    }
    println!("run sql 全成功了？  {:?}  ", 100i32);
}
